﻿using System;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
namespace CRMSystemV2.Common
{
    public static class ExcelHelper
    {
        #region DataTable导出到excel
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dt">源DataTable</param>
        /// <param name="headerText">表头文本</param>
        /// <param name="arrColumnName">自定义列头</param>
        private static MemoryStream ExportFromTable(DataTable dt, string headerText, string[] arrColumnName)
        {
            //创建workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            //取得列宽，如果不设置列宽，此处设置为null
            int[] arrColumnWidth = null;// dt.GetColumnWidth();          
            //设置表头或列头
            int rowIndex = 0;
            IRow row;
            row = sheet.CreateRow(0);
            if (!string.IsNullOrEmpty(headerText))
            {//设置表头                        
                workbook.SetTableHeader(sheet, row, headerText, 0, 0, 0, dt.Columns.Count - 1);
                rowIndex = 1;
                row = sheet.CreateRow(rowIndex);
            }
            if (arrColumnName == null || arrColumnName.Length == 0)
            {//默认列头
                string[] arr = new string[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    arr[i] = dt.Columns[i].ColumnName;
                }
                workbook.SetColumnHeader(sheet, row, arr, arrColumnWidth);
            }
            else
            {//自定义列头
                workbook.SetColumnHeader(sheet, row, arrColumnName, arrColumnWidth);
            }
            //赋值
            ICell cell;
            ICellStyle dateCellStyle = workbook.GetCellStyle(CellDataType.Date);
            foreach (DataRow dr in dt.Rows)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    cell = row.CreateCell(column.Ordinal);
                    string drValue = dr[column].ToString();
                    cell.SetValue(column.DataType.ToString(), drValue, dateCellStyle);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// DataTable导出到Excel的FileStream
        /// </summary>
        /// <param name="dt">源DataTable</param>
        /// <param name="headerText">表头文本</param>
        private static void ExportFromTable(DataTable dt, string headerText, string[] arrColumnName, FileStream fs)
        {
            //创建workbook
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            //取得列宽，如果不设置列宽，此处设置为null
            int[] arrColumnWidth = null;// dt.GetColumnWidth();
            //设置表头或列头
            int rowIndex = 0;
            IRow row;
            row = sheet.CreateRow(0);
            if (!string.IsNullOrEmpty(headerText))
            {//设置表头                        
                workbook.SetTableHeader(sheet, row, headerText, 0, 0, 0, dt.Columns.Count - 1);
                rowIndex = 1;
                row = sheet.CreateRow(rowIndex);
            }
            if (arrColumnName == null || arrColumnName.Length == 0)
            {//默认列头
                string[] arr = new string[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    arr[i] = dt.Columns[i].ColumnName;
                }
                workbook.SetColumnHeader(sheet, row, arr, arrColumnWidth);
            }
            else
            {//自定义列头
                workbook.SetColumnHeader(sheet, row, arrColumnName, arrColumnWidth);
            }
            //赋值
            ICell cell;
            ICellStyle dateCellStyle = workbook.GetCellStyle(CellDataType.Date);
            foreach (DataRow dr in dt.Rows)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    cell = row.CreateCell(column.Ordinal);
                    string drValue = dr[column].ToString();
                    cell.SetValue(column.DataType.ToString(), drValue, dateCellStyle);
                }
            }
            workbook.Write(fs);
            fs.Close();
        }

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dt">源DataTable</param>
        /// <param name="headerText">表头</param>
        /// <param name="arrColumnName">自定义列头</param>
        /// <param name="strFileName">保存位置</param>
        public static void Export2Excel(this DataTable dt, string headerText, string[] arrColumnName, string strFileName)
        {
            string[] temp = strFileName.Split('.');

            if (temp[temp.Length - 1] == "xls" && dt.Columns.Count < 256 && dt.Rows.Count < 65536)
            {//03格式
                using (MemoryStream ms = ExportFromTable(dt, headerText, arrColumnName))
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                }
            }
            else
            {//07格式
                if (temp[temp.Length - 1] == "xls")
                {
                    strFileName = strFileName + "x";
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    ExportFromTable(dt, headerText, arrColumnName, fs);
                }
            }
        }

        #endregion

        #region excel导入到DataTable
        /// <summary>
        /// 读取excel  默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import2Table(string strFileName)
        {
            IWorkbook workbook;
            ISheet sheet;
            try
            {
                workbook = InitWorkbook(strFileName);
                sheet = workbook.GetSheetAt(0);
                DataTable table = ImportFromExcel(sheet, 0, true);
                return table;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return null;
            }
            finally
            {
                sheet = null;
                workbook = null;
            }
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable Import2Table(string strFileName, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook;
            ISheet sheet;
            try
            {
                workbook = InitWorkbook(strFileName);
                sheet = workbook.GetSheet(SheetName);
                DataTable table = ImportFromExcel(sheet, HeaderRowIndex, true);
                return table;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return null;
            }
            finally
            {
                sheet = null;
                workbook = null;
            }
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable Import2Table(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook;
            ISheet sheet;
            try
            {
                workbook = InitWorkbook(strFileName);
                sheet = workbook.GetSheetAt(SheetIndex);
                DataTable table = ImportFromExcel(sheet, HeaderRowIndex, true);
                return table;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return null;
            }
            finally
            {
                sheet = null;
                workbook = null;
            }
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable Import2Table(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
        {
            IWorkbook workbook;
            ISheet sheet;
            try
            {
                workbook = InitWorkbook(strFileName);
                sheet = workbook.GetSheet(SheetName);
                DataTable table = ImportFromExcel(sheet, HeaderRowIndex, needHeader);
                return table;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return null;
            }
            finally
            {
                sheet = null;
                workbook = null;
            }
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable Import2Table(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
        {
            IWorkbook workbook;
            ISheet sheet;
            try
            {
                workbook = InitWorkbook(strFileName);
                sheet = workbook.GetSheetAt(SheetIndex);
                DataTable table = ImportFromExcel(sheet, HeaderRowIndex, needHeader);
                return table;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return null;
            }
            finally
            {
                sheet = null;
                workbook = null;
            }
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        private static DataTable ImportFromExcel(ISheet sheet, int HeaderRowIndex, bool needHeader)
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            if (HeaderRowIndex < 0 || !needHeader)
            {
                headerRow = sheet.GetRow(0);
                cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    DataColumn column = new DataColumn(Convert.ToString(i));
                    table.Columns.Add(column);
                }
            }
            else
            {
                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null)
                    {
                        if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(Convert.ToString(i));
                            table.Columns.Add(column);
                        }

                    }
                    else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                    {
                        DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                        table.Columns.Add(column);
                    }
                    else
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                        table.Columns.Add(column);
                    }
                }
            }
            int rowCount = sheet.LastRowNum;
            for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    row = sheet.CreateRow(i);

                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j <= cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    dataRow[j] = cell.GetValue();
                }
                table.Rows.Add(dataRow);
            }
            return table;
        }

        #endregion

        #region 将DataTable插入excel
        /// <summary>
        /// 将DataTable插入excel
        /// </summary>
        /// <param name="outputFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="dt"></param>
        public static void Insert2Excel(this DataTable dt, string outputFile, string sheetName, string headerText, string[] arrColumnName)
        {
            try
            {
                IWorkbook workbook = InitWorkbook(outputFile);
                //创建sheet
                int num = workbook.GetSheetIndex(sheetName);
                ISheet sheet;
                if (num >= 0)
                    sheet = workbook.GetSheet(sheetName);
                else
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                //取得列宽
                int[] arrColumnWidth = null;// dtSource.GetColumnWidth();            
                //设置表头或列头
                int rowIndex = 0;
                IRow row;
                row = sheet.CreateRow(0);
                if (!string.IsNullOrEmpty(headerText))
                {//设置表头                        
                    workbook.SetTableHeader(sheet, row, headerText, 0, 0, 0, dt.Columns.Count - 1);
                    rowIndex = 1;
                    row = sheet.CreateRow(rowIndex);
                }
                if (arrColumnName == null || arrColumnName.Length == 0)
                {//默认列头
                    string[] arr = new string[dt.Columns.Count];
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        arr[i] = dt.Columns[i].ColumnName;
                    }
                    workbook.SetColumnHeader(sheet, row, arr, arrColumnWidth);
                }
                else
                {//自定义列头
                    workbook.SetColumnHeader(sheet, row, arrColumnName, arrColumnWidth);
                }
                //赋值
                ICell cell;
                ICellStyle dateCellStyle = workbook.GetCellStyle(CellDataType.Date);
                foreach (DataRow dr in dt.Rows)
                {
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dt.Columns)
                    {
                        cell = row.CreateCell(column.Ordinal);
                        string drValue = dr[column].ToString();
                        cell.SetValue(column.DataType.ToString(), drValue, dateCellStyle);
                    }
                }
                using (FileStream fs = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    workbook.Write(fs);
                    fs.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
        }
        #endregion

        #region 更新excel中的数据
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)
        {
            try
            {
                IWorkbook workbook = InitWorkbook(outputFile);
                ISheet sheet = workbook.GetSheet(sheetname);
                IRow row;
                ICell cell;
                for (int i = 0; i < updateData.Length; i++)
                {
                    row = sheet.GetRow(i + rowid);
                    if (row == null)
                    {
                        row = sheet.CreateRow(i + rowid);
                    }
                    cell = row.GetCell(coluid);
                    if (cell == null)
                    {
                        cell = row.CreateCell(coluid);
                    }
                    cell.SetCellValue(updateData[i]);
                }
                using (FileStream fs = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    workbook.Write(fs);
                    fs.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }

        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
        {
            try
            {
                IWorkbook workbook = InitWorkbook(outputFile);
                ISheet sheet = workbook.GetSheet(sheetname);
                IRow row;
                ICell cell;
                for (int j = 0; j < coluids.Length; j++)
                {
                    for (int i = 0; i < updateData[j].Length; i++)
                    {
                        row = sheet.GetRow(i + rowid);
                        if (row == null)
                        {
                            row = sheet.CreateRow(i + rowid);
                        }
                        cell = row.GetCell(coluids[j]);
                        if (cell == null)
                        {
                            cell = row.CreateCell(coluids[j]);
                        }
                        cell.SetCellValue(updateData[j][i]);
                    }
                }
                using (FileStream fs = new FileStream(outputFile, FileMode.Create))
                {
                    workbook.Write(fs);
                    fs.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
        {
            try
            {
                IWorkbook workbook = InitWorkbook(outputFile);
                ISheet sheet = workbook.GetSheet(sheetname);
                IRow row;
                ICell cell;
                for (int i = 0; i < updateData.Length; i++)
                {
                    row = sheet.GetRow(i + rowid);
                    if (row == null)
                    {
                        row = sheet.CreateRow(i + rowid);
                    }
                    cell = row.GetCell(coluid);
                    if (cell == null)
                    {
                        cell = row.CreateCell(coluid);
                    }
                    cell.SetCellValue(updateData[i]);
                }
                using (FileStream fs = new FileStream(outputFile, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                    fs.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="columnids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] columnids, int rowid)
        {
            try
            {
                //FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                IWorkbook workbook = InitWorkbook(outputFile);
                //readfile.Close();
                ISheet sheet = workbook.GetSheet(sheetname);
                IRow row;
                ICell cell;
                for (int j = 0; j < columnids.Length; j++)
                {
                    for (int i = 0; i < updateData[j].Length; i++)
                    {
                        row = sheet.GetRow(i + rowid);
                        if (row == null)
                        {
                            row = sheet.CreateRow(i + rowid);
                        }
                        cell = row.GetCell(columnids[j]);
                        if (cell == null)
                        {
                            cell = row.CreateCell(columnids[j]);
                        }
                        cell.SetCellValue(updateData[j][i]);
                    }
                }
                using (FileStream fs = new FileStream(outputFile, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                    fs.Close();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
        }
        #endregion

        #region 公共方法
        /// <summary>
        /// HSSFWorkbook:支持03格式,XSSFWorkbook：支持07格式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static IWorkbook InitWorkbook(string filePath)
        {
            IWorkbook workbook;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(fs);
                fs.Close();
            }
            return workbook;
        }
        /// <summary>
        /// 判断是否是数字型
        /// </summary>
        /// <param name="message"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        private static bool isNumeric(String message, out double result)
        {
            System.Text.RegularExpressions.Regex rex = new System.Text.RegularExpressions.Regex(@"^[-]?\d+[.]?\d*$");
            result = -1;
            if (rex.IsMatch(message))
            {
                result = double.Parse(message);
                return true;
            }
            else
                return false;
        }
        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValue(this ICell cell)
        {
            if (cell == null)
                return "";
            object value;
            switch (cell.CellType)
            {
                case CellType.STRING:
                    value = cell.StringCellValue;
                    break;
                case CellType.NUMERIC:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        value = DateTime.FromOADate(cell.NumericCellValue);
                    }
                    else
                    {
                        value = Convert.ToDouble(cell.NumericCellValue);
                    }
                    break;
                case CellType.BOOLEAN:
                    value = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.ERROR:
                    value = cell.ErrorCellValue;//ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.FORMULA:
                    value = cell.GetFormulaValue();
                    break;
                default:
                    value = cell.ToString();
                    break;
            }
            return value;
        }
        /// <summary>
        /// 获取FormulaText
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetFormulaValue(this ICell cell)
        {
            object value;
            switch (cell.CachedFormulaResultType)
            {
                case CellType.ERROR:
                    value = cell.ErrorCellValue; //ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.NUMERIC:
                    value = cell.NumericCellValue.ToString();
                    break;
                case CellType.STRING:
                    value = cell.StringCellValue;
                    break;
                default:
                    value = cell.ToString();
                    break;
            }
            return value;
        }
        /// <summary>
        /// 设置值
        /// </summary>
        /// <param name="cell"></param>
        private static void SetValue(this ICell cell, string columnDataType, string value, ICellStyle cellStyle)
        {
            switch (columnDataType)
            {
                case "System.String": //字符串类型
                    double result;
                    if (isNumeric(value, out result))
                    {
                        double.TryParse(value, out result);
                        cell.SetCellValue(result);
                    }
                    else
                    {
                        cell.SetCellValue(value);
                    }
                    break;
                case "System.DateTime": //日期类型
                    DateTime dateV;
                    DateTime.TryParse(value, out dateV);
                    cell.SetCellValue(dateV);
                    cell.CellStyle = cellStyle;//格式化显示
                    break;
                case "System.Boolean": //布尔型
                    bool boolV = false;
                    bool.TryParse(value, out boolV);
                    cell.SetCellValue(boolV);
                    break;
                case "System.Int16": //整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(value, out intV);
                    cell.SetCellValue(intV);
                    break;
                case "System.Decimal": //浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(value, out doubV);
                    cell.SetCellValue(doubV);
                    break;
                case "System.DBNull": //空值处理                           
                default:
                    cell.SetCellValue("");
                    break;
            }
        }
        /// <summary>
        /// 设置表头
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="headerRow"></param>
        /// <param name="headerText"></param>
        /// <param name="fistRow"></param>
        /// <param name="lastRow"></param>
        /// <param name="firstColumn"></param>
        /// <param name="lastColumn"></param>
        private static void SetTableHeader(this IWorkbook workbook, ISheet sheet, IRow headerRow, string headerText, int fistRow, int lastRow, int firstColumn, int lastColumn)
        {
            //设置表头
            headerRow.HeightInPoints = 25;
            ICell cell = headerRow.CreateCell(0);
            cell.SetCellValue(headerText);
            //设置样式
            ICellStyle headStyle = workbook.GetCellStyle(CellDataType.TableHeader);
            cell.CellStyle = headStyle;
            //设置区域
            sheet.AddMergedRegion(new CellRangeAddress(fistRow, lastRow, firstColumn, lastColumn));
        }
        /// <summary>
        /// 设置自定义列头
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="headerRow"></param>
        /// <param name="arrColumnName"></param>
        private static void SetColumnHeader(this IWorkbook workbook, ISheet sheet, IRow headerRow, string[] arrColumnName, int[] arrColumnWidth)
        {
            //设置样式            
            ICellStyle headStyle = workbook.GetCellStyle(CellDataType.ColumnHeader);
            //设置值
            ICell cell;
            if (arrColumnWidth == null || arrColumnWidth.Length == 0)
            {
                //不需要设置列宽
                for (int i = 0; i < arrColumnName.Length; i++)
                {
                    cell = headerRow.CreateCell(i);
                    cell.SetCellValue(arrColumnName[i]);
                    cell.CellStyle = headStyle;
                }               
            }
            else
            {
                //需要设置列宽
                for (int i = 0; i < arrColumnName.Length; i++)
                {
                    cell = headerRow.CreateCell(i);
                    cell.SetCellValue(arrColumnName[i]);
                    cell.CellStyle = headStyle;
                    //设置列宽                    
                    sheet.SetColumnWidth(i, (arrColumnWidth[i] + 1) * 256);
                }
            }
        }
        /// <summary>
        /// 获取列宽
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static int[] GetColumnWidth(this DataTable dt)
        {
            int[] arrColumnWidth = new int[dt.Columns.Count];
            foreach (DataColumn col in dt.Columns)
            {
                arrColumnWidth[col.Ordinal] = Encoding.GetEncoding(936).GetBytes(col.ColumnName).Length;
            }
            int intTemp;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColumnWidth[j])
                    {
                        arrColumnWidth[j] = intTemp;
                    }
                }
            }
            return arrColumnWidth;
        }
        /// <summary>
        /// 获得单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cellDataType"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(this IWorkbook workbook, CellDataType cellDataType)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            switch (cellDataType)
            {
                case CellDataType.TableHeader:
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                    cellStyle.SetFont(workbook.GetFont(cellDataType));
                    break;
                case CellDataType.ColumnHeader:
                    cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                    cellStyle.SetFont(workbook.GetFont(cellDataType));
                    break;
                case CellDataType.Date:
                    IDataFormat format = workbook.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                    break;
            }
            return cellStyle;
        }
        /// <summary>
        /// 获得单元格字体
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cellDataType"></param>
        /// <returns></returns>
        private static IFont GetFont(this IWorkbook workbook, CellDataType cellDataType)
        {
            IFont font = workbook.CreateFont();
            switch (cellDataType)
            {
                case CellDataType.TableHeader:
                    font.FontHeightInPoints = 20;
                    font.Boldweight = 700;
                    break;
                case CellDataType.ColumnHeader:
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    break;
            }
            return font;
        }
        #endregion
    }
    /// <summary>
    /// 单元格类型
    /// </summary>
    enum CellDataType
    {
        TableHeader = 0,
        ColumnHeader = 1,
        Date = 2
    }
}
